﻿CREATE PROCEDURE [dbo].[proc_Department_GetList_CallCenter]
	(
		@CompanyId int,
		@DepartmentId int,
		@StartDate varchar(10),
		@EndDate varchar(10)
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepartmentId

;WITH List As (
	Select ROW_NUMBER() OVER (ORDER BY bmbh)AS Row,	
	Id,
	ParentId,
	(case when len(bmbh)>4 then replicate('┆┄┄',(len(bmbh)-4)/2)+isnull(dname,'') else '◆'+isnull(dname,'') end) as DName,
	Sort,
	CompanyId,
	bmbh
	From Department
	Where CompanyId=@CompanyId 
		And (@DepartmentId=0 Or Left(bmbh,Len(@bmbh_T))=@bmbh_T)
),
List2 As(
	Select DeptId,Sum(Call_In) As Call_In,Sum(Call_Out) As Call_Out
	From CallCenter_Record_Sum
	Where CompanyId=@CompanyId And CreateDate>=@sRq And CreateDate<=@eRq
	Group By DeptId
)
Select a.Id,a.DName,a.Sort,a.bmbh,@CompanyId As CompanyId,
IsNull(b.Call_In,0) As InCount,IsNull(b.Call_Out,0) As OutCount
From List a LEFT JOIN List2 b On a.Id = b.DeptId
Order By a.bmbh
End
